
drop table teacher;
create table teacher(
    tid int primary key ,
    tname varchar2(20) not null,
    phone varchar2(12)
);

drop sequence seq_teacher_tid;
create sequence seq_teacher_tid start with 1;

insert into teacher
values (seq_teacher_tid.nextval,'赵老师','135****3696');
insert into teacher
values (seq_teacher_tid.nextval,'钱老师','150****7471');
insert into teacher
values (seq_teacher_tid.nextval,'孙老师','182****1210');
insert into teacher
values (seq_teacher_tid.nextval,'李老师','137****3363');
insert into teacher
values (seq_teacher_tid.nextval,'周老师','132****5603');
insert into teacher
values (seq_teacher_tid.nextval,'吴老师','188****6919');
insert into teacher
values (seq_teacher_tid.nextval,'郑老师','151****7749');
insert into teacher
values (seq_teacher_tid.nextval,'王老师','187****5131');

select * from teacher;

drop table student;
create table student(
    sid int primary key ,
    sname varchar2(50) not null ,
    age int
);

drop sequence seq_student_sid;
create sequence seq_student_sid start with 101;

insert into student
values (seq_student_sid.nextval,'东方',20);
insert into student
values (seq_student_sid.nextval,'西门',21);
insert into student
values (seq_student_sid.nextval,'南宫',22);
insert into student
values (seq_student_sid.nextval,'北落',19);
insert into student
values (seq_student_sid.nextval,'上官',23);
insert into student
values (seq_student_sid.nextval,'欧阳',20);
insert into student
values (seq_student_sid.nextval,'夏侯',19);
insert into student
values (seq_student_sid.nextval,'拓跋',21);

select * from student;

drop table course;
create table course(
  cid int primary key ,
  title varchar2(50) not null ,
  credit int,
  tid int,
  FOREIGN KEY (tid) REFERENCES teacher (tid)
      ON DELETE CASCADE
);

drop sequence seq_course_cid;
create sequence seq_course_cid start with 1;

insert into course values (seq_course_cid.nextval,'mysql',4,1);
insert into course values (seq_course_cid.nextval,'oracle',2,2);
insert into course values (seq_course_cid.nextval,'ssm',4,3);
insert into course values (seq_course_cid.nextval,'web',3,4);
insert into course values (seq_course_cid.nextval,'C语言',2,5);
insert into course values (seq_course_cid.nextval,'计算机',2,6);
insert into course values (seq_course_cid.nextval,'高数',2,7);
insert into course values (seq_course_cid.nextval,'数据结构',2,8);

select * from course;

drop table student_select_course;
create table student_select_course(
    sscid int primary key ,
    sid int,
    cid int,
    FOREIGN KEY (sid) REFERENCES student (sid)
        ON DELETE CASCADE,
    FOREIGN KEY (cid) REFERENCES course (cid)
        ON DELETE CASCADE
);


insert into student_select_course
values (1,101,1);
insert into student_select_course
values (2,101,2);
insert into student_select_course
values (3,102,2);
insert into student_select_course
values (4,102,3);
insert into student_select_course
values (5,103,1);
insert into student_select_course
values (6,104,2);
insert into student_select_course
values (7,105,4);
insert into student_select_course
values (8,106,5);
insert into student_select_course
values (9,107,6);
insert into student_select_course
values (10,108,7);
insert into student_select_course
values (11,108,8);

select * from student_select_course;

select t.tname,c.title,c.credit from teacher t left join course c on t.tid = c.tid;
/*
 +-----+------+------+
|TNAME|TITLE   |CREDIT|
+-----+------+-------+
|赵老师  |mysql |4     |
|钱老师  |oracle|2     |
|孙老师  |ssm   |4     |
|李老师  |web   |3     |
|周老师  |C语言  |2     |
|吴老师  |计算机  |2     |
|郑老师  |高数   |2     |
|王老师  |数据结构|2     |
+-----+------+------+

 */

select t.tname,c.title ,s.sname ,s.age from student_select_course ssc
    left join course c left join teacher t on c.tid = t.tid on c.cid = ssc.cid
    left join student s  on ssc.sid = s.sid;
/*
+-----+--------+-----+---+
|TNAME  |TITLE   |SNAME|AGE|
+-------+--------+-----+---+
|钱老师  |oracle  |东方   |20 |
|赵老师  |mysql   |东方   |20 |
|孙老师  |ssm     |西门   |21 |
|钱老师  |oracle  |西门   |21 |
|赵老师  |mysql   |南宫   |22 |
|钱老师  |oracle  |北落   |19 |
|李老师  |web     |上官   |23 |
|周老师  |C语言    |欧阳   |20 |
|吴老师  |计算机   |夏侯   |19 |
|王老师  |数据结构  |拓跋  |21 |
|郑老师  |高数     |拓跋  |21 |
+-----+------+-----+---+

 */

select s.sname,c.title from student_select_course ssc
    left join course c on ssc.cid = c.cid
    left join student s on ssc.sid = s.sid;

/*
 +-----+------+
|SNAME |TITLE |
+-----+------+
|东方   |oracle|
|东方   |mysql |
|西门   |ssm   |
|西门   |oracle|
|南宫   |mysql |
|北落   |oracle|
|上官   |web   |
|欧阳   |C语言   |
|夏侯   |计算机   |
|拓跋   |数据结构  |
|拓跋   |高数    |
+-----+------+

 */

select s.sname,t.tname,t.phone from student_select_course ssc
    left join course c left join teacher t on c.tid = t.tid on c.cid = ssc.cid
    left join student s  on ssc.sid = s.sid;

/*
 +-----+-----+-----------+
|SNAME|TNAME  |   PHONE      |
+-----+-------+-----------+
|东方   |钱老师  |150****7471|
|东方   |赵老师  |135****3696|
|西门   |孙老师  |182****1210|
|西门   |钱老师  |150****7471|
|南宫   |赵老师  |135****3696|
|北落   |钱老师  |150****7471|
|上官   |李老师  |137****3363|
|欧阳   |周老师  |132****5603|
|夏侯   |吴老师  |188****6919|
|拓跋   |王老师  |187****5131|
|拓跋   |郑老师  |151****7749|
+-----+-----+-----------+

 */

